package com.sg.hms.DaoImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import com.sg.hms.Dao.SalaryDao;
import com.sg.hms.Model.Employee;
import com.sg.hms.Model.Employee2;
import com.sg.hms.Util.JDBCUtil;
/**
 * 
 * @author lubinghan
 *
 */
public class SalaryDaoImpl implements SalaryDao{
	
	
	@Override
	public int selectSalaryCountByDate(String date) {
		// TODO Auto-generated method stub
		int total = 0;
		Connection conn = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select count(salary) as total from employee,salary,department ");
		sql.append(" where salary.s_elId = employee.elId");
		sql.append(" and employee.dId = department.dId");
		sql.append(" and state = '是'");
		sql.append(" and date_format(salaryDate, '%Y%m')= ?");
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			pst.setString(1, date);
			ResultSet set = pst.executeQuery();
			if (set.next()) {
				total = set.getInt("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return total;
	}
	@Override
	public JSONObject selectSalaryByDate(String date,int pageStart,int pageSize) {
		// TODO Auto-generated method stub
		int total = selectSalaryCountByDate(date);
		JSONObject jsonObject =new JSONObject();
		List<Employee2> employees = new ArrayList<Employee2>();
		Connection conn = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select dName,elName,salary from employee,salary,department ");
		sql.append(" where salary.s_elId = employee.elId");
		sql.append(" and employee.dId = department.dId");
		sql.append(" and state = '否'");
		sql.append(" and date_format(salaryDate, '%Y%m')= ?");
		sql.append(" limit ?,?");
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			pst.setString(1, date);
			pst.setInt(2, pageStart);
			pst.setInt(3, pageSize);
			ResultSet set = pst.executeQuery();
			while (set.next()) {
				Employee2 employee2 = new Employee2();
				employee2.setdName(set.getString("dName"));
				employee2.setElName(set.getString("elName"));
				employee2.setSalary(set.getString("salary"));
				employees.add(employee2);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		jsonObject.put("total", total);
		jsonObject.put("rows", employees);
		return jsonObject;
	}

	@Override
	public List<Employee2> selectDepartSalarySpendByDate(String date,
			int pageStrat, int pageSize) {
		// TODO Auto-generated method stub
		List<Employee2> employees = new ArrayList<Employee2>();
		Connection conn = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select dName,sum(salary) as deparTotalSpend ");
		sql.append(" from salary,employee,department ");
		sql.append(" where salary.s_elId = employee.elId");
		sql.append(" and department.dId = employee.dId");
		sql.append(" and date_format(salaryDate, '%Y%m')= ? group by dName limit ?,?");
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			pst.setString(1, date);
			pst.setInt(2, pageStrat);
			pst.setInt(3, pageSize);
			ResultSet set = pst.executeQuery();
			while (set.next()) {
				Employee2 employee = new Employee2();
				employee.setdName(set.getString("dName"));
				employee.setSalary(set.getString("deparTotalSpend"));
				employees.add(employee);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return employees;
	}
	@Override
	public int selectDepartSalaryDataTotalByDate(String date) {
		// TODO Auto-generated method stub
		int total = 0;
		Connection conn = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select count(dName) as total ");
		sql.append(" from salary,employee,department ");
		sql.append(" where salary.s_elId = employee.elId");
		sql.append(" and department.dId = employee.dId");
		sql.append(" and date_format(salaryDate, '%Y%m')= ? ");
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			pst.setString(1, date);
			ResultSet set = pst.executeQuery();
			if (set.next()) {
				total = set.getInt("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return total;
	}
	@Override
	public int selectCountDepartSpendsByDate(String date) {
		// TODO Auto-generated method stub
		int total = 0;
		//查询出部门申请支出和工资支出两个表表然后继续右连接擦查询
		StringBuffer  sql = new StringBuffer("select COUNT(*) as total ");
		sql.append(" from ( select smoney.dName as TdName, money ,ssalary.dName,totalsalary from ( ");
		sql.append("  select dName,sum(money )as money ");
		sql.append(" from moneyapplication ,application,employee,department  ");
		sql.append(" where  moneyapplication.aId = application.aId  ");
		sql.append(" and application.elId = employee.elId  ");
		sql.append(" and employee.dId = department.dId  ");
		sql.append(" and moneyapplication.state = '已通过'  ");
		sql.append(" and date_format(approveDate, '%Y%m')= ?  ");
		sql.append(" group by dName ) smoney  ");
		sql.append(" right join ( select dName,SUM(salary )as totalsalary ");
		sql.append(" from salary,employee,department  ");
		sql.append(" where salary.s_elId = employee.elId ");
		sql.append(" and department.dId = employee.dId ");
		sql.append(" and date_format(salaryDate, '%Y%m')= ?  ");
		sql.append(" group by dName ) ssalary on smoney.dName = ssalary.dName ");
		sql.append(" UNION select smoney.dName AS TdName, money ,ssalary.dName,totalsalary ");
		sql.append(" from ( select dName,sum(money )as money from moneyapplication ,application,employee,department  ");
		sql.append(" where  moneyapplication.aId = application.aId  ");
		sql.append(" and application.elId = employee.elId  ");
		sql.append(" and employee.dId = department.dId  ");
		sql.append(" and moneyapplication.state = '已通过'  ");
		sql.append(" and date_format(approveDate, '%Y%m')= ?  ");
		sql.append(" group by dName ) smoney ");
		sql.append(" left join ( select dName,SUM(salary )as totalsalary ");
		sql.append(" from salary,employee,department where salary.s_elId = employee.elId ");
		sql.append(" and department.dId = employee.dId ");
		sql.append(" and date_format(salaryDate, '%Y%m')= ? ");
		sql.append(" group by dName ) ssalary on smoney.dName = ssalary.dName ");
		sql.append(" ) sd ");
		Connection conn = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			pst.setString(1, date);
			pst.setString(2, date);
			pst.setString(3, date);
			pst.setString(4, date);
			ResultSet set = pst.executeQuery();
			if (set.next()) {
				total = set.getInt("total");
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return total; 
	}
	@Override
	public int selectTotalSpendsByDate(String date) {
		// TODO Auto-generated method stub
		int totalspends = 0;
		//查询出部门申请支出和工资支出两个表表然后继续右连接擦查询
		StringBuffer  sql = new StringBuffer();
		sql.append("  select smoney.dName as TdName, money ,ssalary.dName,totalsalary from ( ");
		sql.append("  select dName,sum(money )as money ");
		sql.append(" from moneyapplication ,application,employee,department  ");
		sql.append(" where  moneyapplication.aId = application.aId  ");
		sql.append(" and application.elId = employee.elId  ");
		sql.append(" and employee.dId = department.dId  ");
		sql.append(" and moneyapplication.state = '已通过'  ");
		sql.append(" and date_format(approveDate, '%Y%m')= ?  ");
		sql.append(" group by dName ) smoney  ");
		sql.append(" right join ( select dName,SUM(salary )as totalsalary ");
		sql.append(" from salary,employee,department  ");
		sql.append(" where salary.s_elId = employee.elId ");
		sql.append(" and department.dId = employee.dId ");
		sql.append(" and date_format(salaryDate, '%Y%m')= ?  ");
		sql.append(" group by dName ) ssalary on smoney.dName = ssalary.dName ");
		sql.append(" UNION select smoney.dName AS TdName, money ,ssalary.dName,totalsalary ");
		sql.append(" from ( select dName,sum(money )as money from moneyapplication ,application,employee,department  ");
		sql.append(" where  moneyapplication.aId = application.aId  ");
		sql.append(" and application.elId = employee.elId  ");
		sql.append(" and employee.dId = department.dId  ");
		sql.append(" and moneyapplication.state = '已通过'  ");
		sql.append(" and date_format(approveDate, '%Y%m')= ?  ");
		sql.append(" group by dName ) smoney ");
		sql.append(" left join ( select dName,SUM(salary )as totalsalary ");
		sql.append(" from salary,employee,department where salary.s_elId = employee.elId ");
		sql.append(" and department.dId = employee.dId ");
		sql.append(" and date_format(salaryDate, '%Y%m')= ? ");
		sql.append(" group by dName ) ssalary on smoney.dName = ssalary.dName ");
		sql.append("  ");
		Connection conn = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			pst.setString(1, date);
			pst.setString(2, date);
			pst.setString(3, date);
			pst.setString(4, date);
			ResultSet set = pst.executeQuery();
			while (set.next()) {
				totalspends += set.getInt("money");
				totalspends += set.getInt("totalsalary");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return totalspends; 
	}
	@Override
	public double[] selectDepartSpendsByDate(String date) {
		// TODO Auto-generated method stub
		double[] departSpend =new  double[2];
		//查询出部门申请支出和工资支出两个表表然后继续右连接擦查询
		StringBuffer  sql = new StringBuffer("select  sum(money) as money,sum(totalsalary) as totalsalary ");
		sql.append(" from ( select smoney.dName as TdName, money ,ssalary.dName,totalsalary from ( ");
		sql.append("  select dName,sum(money )as money ");
		sql.append(" from moneyapplication ,application,employee,department  ");
		sql.append(" where  moneyapplication.aId = application.aId  ");
		sql.append(" and application.elId = employee.elId  ");
		sql.append(" and employee.dId = department.dId  ");
		sql.append(" and moneyapplication.state = '已通过'  ");
		sql.append(" and date_format(approveDate, '%Y%m')= ?  ");
		sql.append(" group by dName ) smoney  ");
		sql.append(" right join ( select dName,SUM(salary )as totalsalary ");
		sql.append(" from salary,employee,department  ");
		sql.append(" where salary.s_elId = employee.elId ");
		sql.append(" and department.dId = employee.dId ");
		sql.append(" and date_format(salaryDate, '%Y%m')= ?  ");
		sql.append(" group by dName ) ssalary on smoney.dName = ssalary.dName ");
		sql.append(" UNION select smoney.dName AS TdName, money ,ssalary.dName,totalsalary ");
		sql.append(" from ( select dName,sum(money )as money from moneyapplication ,application,employee,department  ");
		sql.append(" where  moneyapplication.aId = application.aId  ");
		sql.append(" and application.elId = employee.elId  ");
		sql.append(" and employee.dId = department.dId  ");
		sql.append(" and moneyapplication.state = '已通过'  ");
		sql.append(" and date_format(approveDate, '%Y%m')= ?  ");
		sql.append(" group by dName ) smoney ");
		sql.append(" left join ( select dName,SUM(salary )as totalsalary ");
		sql.append(" from salary,employee,department where salary.s_elId = employee.elId ");
		sql.append(" and department.dId = employee.dId ");
		sql.append(" and date_format(salaryDate, '%Y%m')= ? ");
		sql.append(" group by dName ) ssalary on smoney.dName = ssalary.dName ");
		sql.append(" ) sd ");
		Connection conn = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			pst.setString(1, date);
			pst.setString(2, date);
			pst.setString(3, date);
			pst.setString(4, date);
			ResultSet set = pst.executeQuery();
			if (set.next()) {
				departSpend[0] = set.getDouble("money");
				departSpend[1] = set.getDouble("totalsalary");
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return departSpend; 
	}
	

}
